﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class UserSearchForm : Form
    {
        SqlConnection sqlCon = new SqlConnection(@"server=WWW-9F0EE1A0959\SQLEXPRESS;integrated security=SSPI;database=MONITOR_db");
        SqlDataAdapter sda = new SqlDataAdapter();
        
        public UserSearchForm()
        {
            InitializeComponent();
        }

        private void UserSearchForm_Load(object sender, EventArgs e)
        {
            DataTable da = new DataTable();
            string strSql = "select id, name, sex, role, idcard, birthday, phone, email, workaddress, homeaddress, comment from tb_user";
            SqlCommand cmd = new SqlCommand(strSql, sqlCon);
            sda.SelectCommand = cmd;
            sda.Fill(da);
            BindingSource bindingSource1 = new BindingSource();
            bindingSource1.DataSource = da;

            this.dataGridView1.DataSource = bindingSource1;
            this.bindingNavigator1.BindingSource = bindingSource1;

            this.dataGridView1.Columns[0].HeaderText = "员工号码";
            this.dataGridView1.Columns[1].HeaderText = "姓名";
            this.dataGridView1.Columns[2].HeaderText = "性别";
            this.dataGridView1.Columns[3].HeaderText = "权限";
            this.dataGridView1.Columns[4].HeaderText = "身份证号码";
            this.dataGridView1.Columns[5].HeaderText = "出生日期";
            this.dataGridView1.Columns[6].HeaderText = "联系电话";
            this.dataGridView1.Columns[7].HeaderText = "电子邮件";
            this.dataGridView1.Columns[8].HeaderText = "工作地址";
            this.dataGridView1.Columns[9].HeaderText = "家庭地址";
            this.dataGridView1.Columns[10].HeaderText = "备注";

           
            
        }

        private string buildsqlstring()
        {
            string strSql = @"select id, name, sex, role, idcard, birthday, phone, email, workaddress, homeaddress, comment from tb_user where id > 0";
            if(this.textBox1.Text != "")
            {
                strSql  += " and " + " id = " + this.textBox1.Text;
            }
            if(this.textBox2.Text != "")
            {
                strSql += " and " + @"name = '" + this.textBox2.Text + @"'";
            }
            if(this.comboBox1.SelectedItem != null)
            {
                if (this.comboBox1.Items[this.comboBox1.SelectedIndex] != "全部")
                {
                    strSql += " and " + @"role = '" + this.comboBox1.Items[this.comboBox1.SelectedIndex] + @"'";
                }
            }
            if (this.textBox3.Text != "")
            {
                strSql += " and " + @"idcard = '" + this.textBox3.Text + @"'";
            }
            if (this.textBox4.Text != "")
            {
                strSql += " and " + @"phone = '" + this.textBox4.Text + @"'";
            }
            if (this.textBox5.Text != "")
            {
                strSql += " and " + @"email = '" + this.textBox5.Text + @"'";
            }


           
            MessageBox.Show(strSql);
            return strSql;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable da = new DataTable();
            string strSql = this.buildsqlstring();
            if (strSql == null)
            {
                return;
            }
            SqlCommand cmd = new SqlCommand(strSql, sqlCon);
            sda.SelectCommand = cmd;
            sda.Fill(da);
            BindingSource bindingSource1 = new BindingSource();
            bindingSource1.DataSource = da;
            this.dataGridView1.DataSource = bindingSource1;
            this.bindingNavigator1.BindingSource = bindingSource1;
        }
    }
}
